INSERT overwrite TABLE jms_dim.dim_cn_three_codes_dt PARTITION (dt='{{ execution_date | cst_ds_nodash }}')
select
    c.id,
    c.staff_id,
    c.staff_code,
    c.staff_name,
    c.code as tail_code,
    c.name as tail_name,
    c.type as tail_type,
    c.receive_coordinates as tail_receive_coordinates,
    c.dispatch_coordinates as tail_dispatch_coordinates,
    c.is_enable as tail_is_enable,
    c.is_delete as tail_is_delete,
    c.create_by as tail_create_by,
    c.update_by as tail_update_by,
    c.create_by_name as tail_create_by_name,
    c.update_by_name as tail_update_by_name,
    c.create_time as tail_create_time,
    c.update_time as tail_update_time,
    c.version as tail_version,
    c.sort as tail_sort,
    d.id as second_code_id,
    d.code as network_second_code,
    d.network_id as network_id,
    d.network_name as network_name,
    d.network_code as network_code,
    d.country_id as country_id,
    d.province_id as province_id,
    d.city_id as city_id,
    d.area_id as tail_id,
    d.town_id as town_id,
    d.is_enable as network_is_enable,
    d.is_delete as network_is_delete,
    d.create_by as network_create_by,
    d.update_by as network_update_by,
    d.create_by_name as network_create_by_name,
    d.update_by_name as network_update_by_name,
    d.create_time as network_create_time,
    d.update_time as network_update_time,
    d.version as network_version,
    d.sort as network_sort
from
    (select
    a.staff_id,
    a.staff_code,
    a.staff_name,
    a.tail_id,
    a.tail_code,
    b.id,
    b.code,
    b.name,
    b.type,
    b.receive_coordinates,
    b.dispatch_coordinates,
    b.is_enable,
    b.is_delete,
    b.create_by,
    b.update_by,
    b.create_by_name,
    b.update_by_name,
    b.create_time,
    b.update_time,
    b.version,
    b.sort,
    b.network_id
    from
     (select * from jms_ods.yl_cn_mysql_lmdm_sys_tail_dispatch_area_staff where dt='{{ execution_date | cst_ds_nodash }}') a
    full join
     (select * from jms_ods.yl_cn_mysql_lmdm_sys_tail_dispatch_area where dt='{{ execution_date | cst_ds_nodash }}' and is_enable=1 and is_delete=1) b
    on a.tail_id = b.id) c
full join
  (select * from jms_ods.yl_cn_mysql_lmdm_sys_second_code where dt='{{ execution_date | cst_ds_nodash }}' and is_enable=1 and is_delete=1) d
 on c.network_id=d.network_id
  distribute by 1